﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DataSystem
{
    public class Connection
    {
        SqlConnection conn;


        public Connection(string connstring)
        {
            conn = new SqlConnection(connstring);
        }




        public List<Dictionary<string, object>> GetData(string sql)
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                // create a new data adapter based on the specified query.
                SqlDataAdapter da = new SqlDataAdapter();
                //set the SelectCommand of the adapter
                da.SelectCommand = cmd;
                // create a new DataTable
                DataTable dt = new DataTable();
                //fill the DataTable
                da.Fill(dt);
                //return the DataTable

                List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();

                foreach (DataRow dr in dt.Rows)
                {
                    Dictionary<string, object> row = new Dictionary<string, object>();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        object val = dr[dc.ColumnName];
                        if (val == DBNull.Value) { val = null; }
                        row[dc.ColumnName] = val;
                    }
                    data.Add(row);
                }
                return data;

            }
            catch { return null; }
        }
    }
}
